<?php
error_reporting(E_ALL ^ E_NOTICE);
set_time_limit(0);
$db_name = "dinocap2";

$table_name = "dinocap2hd";

$connection = @mysql_connect("localhost","root","root") or die(mysql_error());

$db = @mysql_select_db($db_name, $connection) or die(mysql_error());


$logdate = mysql_query("SELECT DISTINCT whatday FROM `dinocap2hd`");

while ($everylogdate = mysql_fetch_array($logdate)) {
 



    //weekPayARPU 周付费用户ARPU值 =周付费总额/周付费人数
    $weekPayARPU_query = mysql_query("select (select sum(dayIapMoney) from dinocap2hd where YEARWEEK( date_format( whatday, '%Y-%m-%d' ) ) = YEARWEEK( now( ) ))/(select count(distinct deviceid) from dinocap2hd where YEARWEEK( date_format( whatday, '%Y-%m-%d' ) ) = YEARWEEK( now( ) ) and dayIapMoney > 0)");

     $weekPayARPU = mysql_fetch_array($weekPayARPU_query);

    $weekActiveARPU_query = mysql_query("select (select sum(dayIapMoney) from dinocap2hd where YEARWEEK( date_format( whatday, '%Y-%m-%d' ) ) = YEARWEEK( now( ) ))/(select count(distinct deviceid) from dinocap2hd where YEARWEEK( date_format( whatday, '%Y-%m-%d' ) ) = YEARWEEK( now( ) ) and dayPlayTime >2)");
    $weekActiveARPU = mysql_fetch_array($weekActiveARPU_query);
   
    //weekLogARPU 周登陆用户ARPU值  = 周付费用户/周登陆人数(活跃用户+新用户)  
    $weekLodPesonrNums_query = mysql_query("select (select count(dayIapMoney) from dinocap2hd where YEARWEEK( date_format( whatday, '%Y-%m-%d' ) ) = YEARWEEK( now( ) ))/(select count(distinct deviceid) from dinocap2hd where YEARWEEK( date_format( whatday, '%Y-%m-%d' ) ) = YEARWEEK( now( ) ))");
    $weekLodPesonrNums = mysql_fetch_array($weekLodPesonrNums_query);

    $weekLogARPU_query = mysql_query("select (select count(dayIapMoney) from dinocap2hd where YEARWEEK( date_format( whatday, '%Y-%m-%d' ) ) = YEARWEEK( now( ) ))/(select count(distinct deviceid) from dinocap2hd where YEARWEEK( date_format( whatday, '%Y-%m-%d' ) ) = YEARWEEK( now( ) ))");
    $weekLogARPU = mysql_fetch_array($weekLogARPU_query);

    //weekWastageRate  周活跃用户流失率 上周活跃用户在本周没有登陆过游戏的用户数，除以上周的活跃人数。  
    $weekWastageRate_query = mysql_query("select (select sum(deviceid) from dinocap2hd where (SELECT distinct deviceid FROM dinocap2hd   WHERE YEARWEEK(date_format(whatday,'%Y-%m-%d')) = YEARWEEK(now())-1 and dayIapMoney >0 and dayPlayTime >2) not in (SELECT  distinct deviceid FROM dinocap2hd WHERE YEARWEEK( date_format( whatday, '%Y-%m-%d' ) ) = YEARWEEK( now( ) )))/(SELECT count(distinct deviceid) FROM dinocap2hd   WHERE YEARWEEK(date_format(whatday,'%Y-%m-%d')) = YEARWEEK(now())-1 and dayIapMoney >0 and dayPlayTime >2)");
    $weekWastageRate = mysql_fetch_array($weekWastageRate_query);

    //weekPayRate  周活跃用户付费率 周付费用户数除以周活跃人数

    $weekPayRate_query = mysql_query("select (select count(distinct deviceid) from dinocap2hd where YEARWEEK( date_format( whatday, '%Y-%m-%d' ) ) = YEARWEEK( now( ) ) and dayIapMoney > 0)/(SELECT count(distinct deviceid) FROM dinocap2hd   WHERE YEARWEEK(date_format(whatday,'%Y-%m-%d')) = YEARWEEK(now())-1 and dayIapMoney >0 and dayPlayTime >2)");
    $weekPayRate = mysql_fetch_array($weekPayRate_query);

    //weekLogPayRate周登陆用户付费率 周付费用户数除以周登陆人数
    $weekLogPayRate_query = mysql_query("select (select count(distinct deviceid) from dinocap2hd where YEARWEEK( date_format( whatday, '%Y-%m-%d' ) ) = YEARWEEK( now( ) ) and dayIapMoney > 0)/(select count(distinct deviceid) from dinocap2hd where YEARWEEK( date_format( whatday, '%Y-%m-%d' ) ) = YEARWEEK( now( ) ))");
    $weekLogPayRate = mysql_fetch_array($weekLogPayRate_query);

    //weekPayWastageRate  周付费用户流失率 上周有付费行为而本周7天未登陆的用户数除以上周的付费用户数    //sql查询慢,需要优化
    $weekPayWastageRate_query = mysql_query("select (select count(deviceid) FROM  dinocap2hd where (SELECT distinct deviceid FROM dinocap2hd   WHERE YEARWEEK(date_format(whatday,'%Y-%m-%d')) = YEARWEEK(now())-1 and dayIapMoney >0) not in (SELECT  distinct deviceid FROM dinocap2hd WHERE YEARWEEK( date_format( whatday, '%Y-%m-%d' ) ) = YEARWEEK( now( ) )))/(SELECT distinct deviceid FROM dinocap2hd   WHERE YEARWEEK(date_format(whatday,'%Y-%m-%d')) = YEARWEEK(now())-1 and dayIapMoney >0)");
    $weekPayWastageRate = mysql_fetch_array($weekPayWastageRate_query);

    //本周总充值笔数除
     $weekpayTimes_query = mysql_query("SELECT count(id)  FROM  dinocap2hd  where  dayIapMoney >0 and  DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(whatday) and whatday = '$everylogdate[whatday]'");
     $weekpayTimes = mysql_fetch_array($weekpayTimes_query);
  
    //$weekPayPesonrTime = 0; //周付费用户人均充值笔数 (本周总充值笔数除以付费用户人数)
    $weekPayPesonrTime_query = mysql_query("SELECT (SELECT sum(dayPlayTime)  FROM  dinocap2hd  where  dayIapMoney >0 and  DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(whatday) and whatday = '$everylogdate[whatday]')/(SELECT count(id)  FROM  dinocap2hd  where  dayIapMoney >0 and  DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(whatday) and whatday = '$everylogdate[whatday]')");
    $weekPayPesonrTime = mysql_fetch_array($weekPayPesonrTime_query);

                 $sql = "UPDATE report SET weekPayARPU='$weekPayARPU[0]' ,weekActiveARPU ='$weekActiveARPU[0]',
                          weekLogARPU = '$weekLogARPU[0]',weekPayRate = '$weekPayRate[0]',weekPayPesonrTime ='$weekPayPesonrTime[0]',
                          weekPayWastageRate = '$weekPayWastageRate[0]',weekWastageRate = '$weekWastageRate[0]'
                          WHERE logdate='$everylogdate[0]'"; 

$query= @mysql_query($sql,$connection) or die(mysql_error());  


}

?>
